﻿
--
-- Create schema accountability4net
--
/****** Object:  Database [accountability4net]    Script Date: 01/26/2010 09:43:18 ******/
USE [MASTER]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'accountability4net')
BEGIN
	DROP DATABASE [accountability4net]
END
GO
CREATE DATABASE accountability4net
GO
USE [accountability4net]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Account_AccountAction]') AND parent_object_id = OBJECT_ID(N'[dbo].[AccountAction]'))
ALTER TABLE [dbo].[AccountAction] DROP CONSTRAINT [FK_Account_AccountAction]
GO
USE [accountability4net]
GO
/****** Object:  Table [dbo].[AccountAction] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountAction]') AND type in (N'U'))
DROP TABLE [dbo].[AccountAction]
GO

/****** Object:  Table [dbo].[AccountTransaction] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountTransaction]') AND type in (N'U'))
DROP TABLE [dbo].[AccountTransaction]

/****** Object:  Table [dbo].[Account]    Script Date: 01/26/2010 09:42:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))
DROP TABLE [dbo].[Account]
GO

CREATE TABLE [dbo].[Account] (
  [Number] int NOT NULL,
  [Type] tinyint NOT NULL, /*enum('ASSET','LIABILITY','INCOME','EXPENSE')*/
  [Balance] decimal(10,4) NOT NULL /*default '0.0000'*/,
  [Name] nvarchar(255) NULL,
  [Description] nvarchar(3072) NULL,
  CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[Number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
-- Add Constraints
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_Balance] DEFAULT (0.0000) FOR [Balance]
GO

-- =====
-- Create Table AccountTransaction
-- =====
CREATE TABLE [AccountTransaction] (
  [Id] [uniqueidentifier] NOT NULL,
  [CreatedDate] datetime NOT NULL,
  [CreatedMilliseconds] bigint NOT NULL,
  [Description] nvarchar(3072) NULL,
  [Commited] bit NOT NULL,
CONSTRAINT [PK_AccountTransaction] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
-- Add Constraints
ALTER TABLE [dbo].[AccountTransaction] ADD CONSTRAINT [DF_AccountTransaction_CreatedDate] DEFAULT (GETDATE()) FOR [CreatedDate]
GO

-- =====
-- Create Table AccountAction
-- =====
CREATE TABLE [dbo].[AccountAction] (
  [Id] bigint NOT NULL IDENTITY(1, 1),
  [Transaction_id] uniqueidentifier NULL,
  [Type] tinyint NOT NULL, /* enum('DEBIT','CREDIT') */
  [Amount] decimal(10,4) NOT NULL,
  [Account_id] int NOT NULL,
  CONSTRAINT [PK_AccountAction] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [contact] (
  [id] varchar(255) NOT NULL,
  [email] varchar(255),
  [name.familyName] varchar(255) NULL,
  [name.givenName] varchar(255),
  [name.additionalNames] varchar(255),
  [name.namePrefix] varchar(255),
  [name.nameSuffix] varchar(255),
  [name.formattedName] varchar(255),
  [photo] varbinary(MAX),
  CONSTRAINT [PK_contact] PRIMARY KEY CLUSTERED
  (
	[id] ASC
  ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
-- Add Constraints
ALTER TABLE [dbo].[AccountAction] ADD CONSTRAINT [DF_AccountAction_TransactionId] DEFAULT (NULL) FOR [Transaction_id]
GO
-- Add Foreign Keys
ALTER TABLE [dbo].[AccountAction]  WITH CHECK ADD  CONSTRAINT [FK_Account_AccountAction] FOREIGN KEY([Account_id])
REFERENCES [dbo].[Account] ([Number])
GO
ALTER TABLE [dbo].[AccountAction]  WITH CHECK ADD  CONSTRAINT [FK_AccountTransaction_AccountAction] FOREIGN KEY([Transaction_id])
REFERENCES [dbo].[AccountTransaction] ([Id])
GO